/**
 * Copyright (c) 2015-2017, Henry Yang 杨勇 (gismail@foxmail.com).
 * <p>
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 * <p>
 * http://www.apache.org/licenses/LICENSE-2.0
 * <p>
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */
package com.lambkit.plugin.activerecord.dialect;

import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.util.ArrayUtil;
import cn.hutool.core.util.StrUtil;
import com.jfinal.plugin.activerecord.IRow;
import com.jfinal.plugin.activerecord.SqlPara;
import com.jfinal.plugin.activerecord.dialect.Sqlite3Dialect;
import com.lambkit.db.Sql;
import com.lambkit.db.sql.*;

import java.util.List;
import java.util.Set;

public class LambkitSqlite3Dialect extends Sqlite3Dialect implements LambkitDialect {

	public String forLimitSql(Object limit) {
		StringBuilder sqlBuilder = new StringBuilder();
		if (limit != null) {
			sqlBuilder.append(" LIMIT " + limit);
		}
		return sqlBuilder.toString();
	}

	@Override
	public String forFindSql(String sql, String orderBy, Object limit) {
		StringBuilder sqlBuilder = new StringBuilder(sql);
		if (orderBy != null) {
			sqlBuilder.append(" ORDER BY ").append(orderBy);
		}
		sqlBuilder.append(forLimitSql(limit));
		return sqlBuilder.toString();
	}

	@Override
	public SqlPara forFindBySqlPara(SqlPara sqlPara, String orderBy, Object limit) {
		StringBuilder sqlBuilder = new StringBuilder(sqlPara.getSql());
		if (orderBy != null) {
			sqlBuilder.append(" ORDER BY ").append(orderBy);
		}
		sqlBuilder.append(forLimitSql(limit));
		sqlPara.setSql(sqlBuilder.toString());
		return sqlPara;
	}

	@Override
	public SqlPara forDeleteByExample(Example example) {
		Sql sqlPara = new Sql();
		StringBuilder sqlBuilder = new StringBuilder("DELETE FROM ");
		sqlBuilder.append(example.getTableName()).append(" ");
		//sqlBuilder.append("as ").append(example.getAlias()).append(" ");
		example.setAlias(null);
		appIfJoinNotEmpty(example, sqlBuilder);
		appExampleConditions(example, sqlBuilder);
		// appConditions(example.getColumnsList(), sqlBuilder);
		sqlPara.setSql(sqlBuilder.toString());
		example.addValueToParam(sqlPara);
		return toSqlPara(sqlPara);
	}

//	@Override
//	public SqlPara forUpdate(Record record, String tableName, QueryParas queryParas) {
//		SqlPara sqlPara = new SqlPara();
//		String[] columnNames = record.getColumnNames();
//		if (columnNames.length > 0) {
//			StringBuilder sqlBuilder = new StringBuilder("UPDATE ");
//			sqlBuilder.append(tableName).append(" SET ");
//			int i = 0;
//			for (String name : columnNames) {
//				sqlBuilder.append(name).append("=?");
//				if (i > 0) {
//					sqlBuilder.append(", ");
//				} else {
//					sqlBuilder.append(" ");
//				}
//				i++;
//				sqlPara.addPara(record.get(name));
//			}
//			String sqlExceptSelect = queryParas.getSqlExceptSelect();
//			sqlExceptSelect = sqlExceptSelect.substring(sqlExceptSelect.indexOf("where"));
//			sqlBuilder.append(sqlExceptSelect);
//			List<Object> paraList = queryParas.getParaList();
//			for (int j = 0; j < paraList.size(); j++) {
//				sqlPara.addPara(paraList.get(j));
//			}
//		}
//		return sqlPara;
//	}

	@Override
	public SqlPara forUpdateByExample(IRow record, Example example) {
		Sql sqlPara = new Sql();
		Set<String> attrNameSet = record.toMap().keySet();
		String[] columnNames = attrNameSet.toArray(new String[attrNameSet.size()]);
		if (columnNames.length > 0) {
			StringBuilder sqlBuilder = new StringBuilder("UPDATE ");
			sqlBuilder.append(example.getTableName());
			sqlBuilder.append("as ").append(example.getAlias()).append(" ");
			appIfJoinNotEmpty(example, sqlBuilder);
			sqlBuilder.append(" SET ");
			int i = 0;
			for (String name : columnNames) {
				sqlBuilder.append(name).append("=?");
				if (i > 0) {
					sqlBuilder.append(", ");
				} else {
					sqlBuilder.append(" ");
				}
				i++;
				sqlPara.addPara(record.get(name));
			}

			appExampleConditions(example, sqlBuilder);
			// appConditions(example.getColumnsList(), sqlBuilder);
			sqlPara.setSql(sqlBuilder.toString());
			example.addValueToParam(sqlPara);
		}
		return toSqlPara(sqlPara);
	}

	@Override
	public SqlPara forFindByExample(Example example, Object limit) {
		Sql sqlPara = new Sql();
		StringBuilder sqlBuilder = new StringBuilder("SELECT ");
		sqlBuilder.append(example.getSelectSql());
		sqlBuilder.append(" FROM ");
		sqlBuilder.append(example.getTableName()).append(" ");
		sqlBuilder.append("as ").append(example.getAlias()).append(" ");

		appIfJoinNotEmpty(example, sqlBuilder);
		appExampleConditions(example, sqlBuilder);
		// appConditions(example.getColumnsList(), sqlBuilder);

		if(example.getGroupBy() != null) {
			sqlBuilder.append(" GROUP BY ").append(example.getGroupBy().getName());
			if(example.getGroupBy().getHaving()!=null) {
				StringBuilder havingSql = new StringBuilder();
				appConditions("", example.getGroupBy().getHaving(), havingSql);
				if(havingSql.length() > 1) {
					sqlBuilder.append(" HAVING ").append(havingSql);
				}
			}
		}

		if (example.getOrderBy() != null) {
			sqlBuilder.append(" ORDER BY ").append(getOrderby(example.getAlias(), example.getOrderBy()));
		}

		sqlBuilder.append(forLimitSql(limit));
		sqlPara.setSql(sqlBuilder.toString());

		example.addValueToParam(sqlPara);
		return toSqlPara(sqlPara);
	}

	@Override
	public SqlPara forPaginateByExample(Example example) {

		SqlPara sqlPara = forPaginateFormByExample(example);
		StringBuilder sqlBuilder = new StringBuilder("SELECT ");
		sqlBuilder.append(example.getSelectSql());
		sqlBuilder.append(sqlPara.getSql());
		sqlPara.setSql(sqlBuilder.toString());
		return sqlPara;
	}

	@Override
	public SqlPara forPaginateFormByExample(Example example) {
		Sql sqlPara = new Sql();
		StringBuilder sqlBuilder = new StringBuilder(" FROM ");
		sqlBuilder.append(example.getTableName()).append(" ");
		sqlBuilder.append("as ").append(example.getAlias()).append(" ");

		appIfJoinNotEmpty(example, sqlBuilder);
		appExampleConditions(example, sqlBuilder);
		// appConditions(example.getColumnsList(), sqlBuilder);

		if (example.getOrderBy() != null) {
			sqlBuilder.append(" ORDER BY ").append(getOrderby(example.getAlias(), example.getOrderBy()));
		}

		sqlPara.setSql(sqlBuilder.toString());

		example.addValueToParam(sqlPara);
		return toSqlPara(sqlPara);
	}

	@Override
	public String forFindByColumns(String table, String loadColumns, Columns columns, String orderBy, Object limit) {
		StringBuilder sqlBuilder = new StringBuilder("SELECT ");
		sqlBuilder.append(loadColumns).append(" FROM ").append(table).append(" ");

		appIfNotEmpty(table, columns, sqlBuilder, true);

		if (orderBy != null) {
			sqlBuilder.append(" ORDER BY ").append(getOrderby(table, orderBy));
		}

		if (limit != null) {
			sqlBuilder.append(" LIMIT " + limit);
		}

		return sqlBuilder.toString();
	}

	@Override
	public String forPaginateSelect(String loadColumns) {
		return "SELECT " + loadColumns;
	}

	@Override
	public String forPaginateByColumns(String table, Columns columns, String orderBy) {
		StringBuilder sqlBuilder = new StringBuilder(" FROM ").append(table);

		appIfNotEmpty(table, columns, sqlBuilder, true);

		if (orderBy != null) {
			sqlBuilder.append(" ORDER BY ").append(getOrderby(table, orderBy));
		}

		return sqlBuilder.toString();
	}

	/*
	 * private boolean appConditions(String alias, List<Columns> columns,
	 * StringBuilder sqlBuilder) { StringBuilder wsb = new StringBuilder(); if
	 * (CollUtil.isNotEmpty(columns)) { int index = 0; for (Columns column :
	 * columns) { if (CollUtil.isNotEmpty(column.getList())) { if (index > 0) {
	 * wsb.append(" ").append(column.getOutJunc()).append(" "); } wsb.append("(");
	 * appIfNotEmpty(alias, column, wsb, false); wsb.append(")"); // if (index !=
	 * columns.size() - 1) { // wsb.append(" OR "); // } index++; }//if } }
	 * if(wsb.length() > 3) { sqlBuilder.append(" WHERE "); sqlBuilder.append(wsb);
	 * return true; } return false; }
	 */
	private boolean appConditions(String alias, List<ColumnsGroup> columns, StringBuilder sqlBuilder) {
		StringBuilder wsb = new StringBuilder();
		if (CollUtil.isNotEmpty(columns)) {
			int gindex = 0;
			for (ColumnsGroup columnsGroup : columns) {
				if (gindex > 0) {
					wsb.append(" ").append(columnsGroup.getOutJunc()).append(" (");
				}
				appConditions(alias, columnsGroup, wsb);
//            	int index = 0;
//            	Columns groupColumn = columnsGroup;
//            	System.out.println("columns: " + groupColumn.getList().size());
//            	if (CollUtil.isNotEmpty(groupColumn.getList())) {
//            		if (index  > 0) {
//                    	wsb.append(" ").append(groupColumn.getOutJunc()).append(" ");
//                    }
//            		wsb.append("(");
//                    appIfNotEmpty(alias, groupColumn, wsb, false);
//                    wsb.append(")");
////                  if (index != columns.size() - 1) {
////                      wsb.append(" OR ");
////                  }
//                    index++;
//            	}//if
//            	System.out.println("columnsGroup: " + columnsGroup.getOredColumns().size());
//            	for (Columns column : columnsGroup.getOredColumns()) {
//            		if (CollUtil.isNotEmpty(column.getList())) {
//                		if (index  > 0) {
//                        	wsb.append(" ").append(column.getOutJunc()).append(" ");
//                        }
//                		wsb.append("(");
//                        //appIfNotEmpty(alias, column, wsb, false);
//                		appConditions(alias, columnsGroup, wsb);
//                        wsb.append(")");
////                      if (index != columns.size() - 1) {
////                          wsb.append(" OR ");
////                      }
//                        index++;
//                	}//if
//                }
				if (gindex > 0) {
					wsb.append(")");
				}
				gindex++;
			}

		}
		if (wsb.length() > 3) {
			sqlBuilder.append(" WHERE ");
			sqlBuilder.append(wsb);
			return true;
		}
		return false;
	}

	private boolean appConditions(String alias, ColumnsGroup group, StringBuilder sqlBuilder) {
		StringBuilder wsb = new StringBuilder();
		int index = 0;
		Columns groupColumn = group;
		// System.out.println("columns0: " + groupColumn.getList().size());
		if (ArrayUtil.isNotEmpty(groupColumn.getList())) {
			if (index > 0) {
				wsb.append(" ").append(groupColumn.getOutJunc()).append(" ");
			}
			wsb.append("(");
			appIfNotEmpty(alias, groupColumn, wsb, false);
			wsb.append(")");
			index++;
		} // if
		for (Columns columns : group.getOredColumns()) {
			// System.out.println("columns1: " + columns.getList().size());
			if (columns instanceof ColumnsGroup) {
				ColumnsGroup columnsGroup = (ColumnsGroup) columns;
				// System.out.println("columnsGroup1: " + columnsGroup.getOredColumns().size());
				if (ArrayUtil.isNotEmpty(columnsGroup.getList())
						|| ArrayUtil.isNotEmpty(columnsGroup.getOredColumns())) {
					if (index > 0) {
						wsb.append(" ").append(columnsGroup.getOutJunc()).append(" ");
					}
					wsb.append("(");
					appConditions(alias, columnsGroup, wsb);
					wsb.append(")");
					index++;
				}
			} else if (ArrayUtil.isNotEmpty(columns.getList())) {
				if (index > 0) {
					wsb.append(" ").append(columns.getOutJunc()).append(" ");
				}
				wsb.append("(");
				appIfNotEmpty(alias, columns, wsb, false);
				wsb.append(")");
				index++;
			} // if
		}
		if (wsb.length() > 3) {
			sqlBuilder.append(wsb);
			return true;
		}
		return false;
	}

	private void appIfNotEmpty(String alias, Columns columns, StringBuilder sqlBuilder, boolean bAppendWhere) {
		if (columns == null) {
			return;
		}
		List<Column> columnList = columns.getList();
		if (ArrayUtil.isNotEmpty(columnList)) {
			if (bAppendWhere) {
				sqlBuilder.append(" WHERE ");
			}
			int index = 0;
			for (Column column : columnList) {
				if (index > 0) {
					sqlBuilder.append(" ").append(columns.getInJunc()).append(" ");
				}
				if(StrUtil.isNotBlank(alias)) {
					sqlBuilder.append(String.format(" %s.%s %s ", alias, column.getName(), getCondition(column)));
				} else {
					sqlBuilder.append(String.format(" %s %s ", column.getName(), getCondition(column)));
				}
//                if (index != columnList.size() - 1) {
//                    sqlBuilder.append(" AND ");
//                }
				index++;
			}
		}
	}

	private void appIfNotEmpty4Group(String alias, ColumnsGroup groupColumn, StringBuilder sqlBuilder,
			boolean bAppendWhere) {
		if (groupColumn == null) {
			return;
		}
		if (ArrayUtil.isNotEmpty(groupColumn.getList()) || ArrayUtil.isNotEmpty(groupColumn.getOredColumns())) {
			if(bAppendWhere) {
      			sqlBuilder.append(" WHERE ");
      		} else {
      			sqlBuilder.append(" AND ");
      		}
		}
		appConditions(alias, groupColumn, sqlBuilder);
	}

	private void appExampleConditions(Example example, StringBuilder sqlBuilder) {
		boolean flag = appConditions(example.getAlias(), example.getColumnsList(), sqlBuilder);
		if (ArrayUtil.isNotEmpty(example.getJoinOnList())) {
			int i = 0;
			for (JoinOn join : example.getJoinOnList()) {
				if (i == 0) {
					appIfNotEmpty4Group(join.getJoinAlias(), join.getColumnsGroup(), sqlBuilder, !flag);
				} else {
					appIfNotEmpty4Group(join.getJoinAlias(), join.getColumnsGroup(), sqlBuilder, false);
				}
				i++;
			}
		}
	}

	private void appIfJoinNotEmpty(Example example, StringBuilder sqlBuilder) {
		if (ArrayUtil.isNotEmpty(example.getJoinOnList())) {
			for (JoinOn join : example.getJoinOnList()) {
				if (join.getType() == SqlJoinMode.LEFT_JOIN) {
					sqlBuilder.append(" LEFT JOIN ");
				} else if (join.getType() == SqlJoinMode.RIGHT_JOIN) {
					sqlBuilder.append(" RIGHT JOIN ");
				} else {
					sqlBuilder.append(" INNER JOIN ");
				}
				sqlBuilder.append(" ").append(join.getJoinTableName()).append(" as ");
            	sqlBuilder.append(" ").append(join.getJoinAlias()).append(" ON ");
				String mainAlias = join.getMainAlias();
				String alias = join.getJoinAlias();
				int i = 0;
				for (JoinOnField jof : join.getJoinOnFields()) {
					if (i > 0) {
						sqlBuilder.append(" and ");
					}
					sqlBuilder.append(" ").append(mainAlias).append(".").append(jof.getMainField()).append(" ");
					sqlBuilder.append("=");
					sqlBuilder.append(" ").append(alias).append(".").append(jof.getJoinField()).append(" ");
				}
			}
		}
	}

	private String getOrderby(String table, String orderby) {
		if(orderby.contains(".")) {
			return orderby;
		}
		StringBuilder sqlBuilder = new StringBuilder();
		sqlBuilder.append(table).append(".");
		orderby = orderby.replaceAll(",", "," + table + ".");
		sqlBuilder.append(orderby);
		return sqlBuilder.toString();
	}

	private String getCondition(Column column) {
		if (column.isNoValue()) {
			if (ConditionMode.EMPTY.equals(column.getLogic())) {
				return " = '' ";
			} else if (ConditionMode.NOT_EMPTY.equals(column.getLogic())) {
				return " <> '' ";
			} else if (ConditionMode.ISNULL.equals(column.getLogic())) {
				return " is null ";
			} else if (ConditionMode.NOT_NULL.equals(column.getLogic())) {
				return " is not null ";
			}
		} else if (column.isSingleValue()) {
			if (ConditionMode.EQUAL.equals(column.getLogic())) {
				return " = ? ";
			} else if (ConditionMode.NOT_EQUAL.equals(column.getLogic())) {
				return " <> ? ";
			} else if (ConditionMode.LESS_THEN.equals(column.getLogic())) {
				return " < ? ";
			} else if (ConditionMode.LESS_EQUAL.equals(column.getLogic())) {
				return " <= ? ";
			} else if (ConditionMode.GREATER_THEN.equals(column.getLogic())) {
				return " > ? ";
			} else if (ConditionMode.GREATER_EQUAL.equals(column.getLogic())) {
				return " >= ? ";
			} else if (ConditionMode.FUZZY.equals(column.getLogic())) {
				return " like ? ";
			} else if (ConditionMode.NOT_FUZZY.equals(column.getLogic())) {
				return " not like ? ";
			}
		} else if (column.isBetweenValue()) {
			if (ConditionMode.BETWEEN.equals(column.getLogic())) {
				return " between ? and ? ";
			} else if (ConditionMode.NOT_BETWEEN.equals(column.getLogic())) {
				return " not between ? and ? ";
			}
		} else if (column.isListValue()) {
			StringBuilder str = new StringBuilder();
			if (ConditionMode.IN.equals(column.getLogic())) {
				str.append(" in ");
			} else if (ConditionMode.NOT_IN.equals(column.getLogic())) {
				str.append(" not in ");
			}
			int j = 0;
			for (Object val : (List<?>) column.getValue()) {
				if (j == 0) {
					str.append(" (?");
				} else {
					str.append(",?");
				}
				j++;
			}
			str.append(")");
			return str.toString();
		}
		return "";
	}

	public String tableBuilderDoBuild(String tableName) {
		return "select * from " + tableName + " where 1 = 2";
	}
}
